package pl.wroc.uni.ii.evolution.servlets.databasesupport.providers;

import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import oracle.jdbc.OracleResultSet;
import oracle.sql.BLOB;
import pl.wroc.uni.ii.evolution.servlets.databasesupport.structure.EvIndividualInfo;
import pl.wroc.uni.ii.evolution.servlets.databasesupport.structure.EvTaskInfo;

public class EvOracleDatabaseProvider implements EvDatabaseProvider {


  protected Connection conn;



  /** *************************************************************************** */

  public void connect(String database_url, String database_user,
      String database_password) throws SQLException {
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    conn = DriverManager.getConnection(database_url, database_user,
        database_password);
    conn.setAutoCommit(false);
  }

  public void disconnect() throws SQLException {
    conn.close();
  }

  /** *************************************************************************** */

  public int addIndividual(Object individual, long task_id,
      double objective_value, long cell_id, long node_id) throws IOException {
    int ID = -1;

    try {
      Statement stmt = conn.createStatement();
      String s = "SELECT individual_seq.NEXTVAL FROM DUAL";
      ResultSet rs = stmt.executeQuery(s);

      if (rs.next())
        ID = rs.getInt(1);
      rs.close();

      if (ID != -1) {
        s = "INSERT INTO individual(id, instance_id, objective_value, individual, creation_cell, creation_node, creation_time) VALUES("
            + ID
            + ", "
            + task_id
            + ", "
            + objective_value
            + ", EMPTY_BLOB(), "
            + cell_id + ", " + node_id + ", CURRENT_TIMESTAMP)";
        stmt.executeUpdate(s);

        s = "SELECT individual FROM individual WHERE id = " + ID
            + " FOR UPDATE";
        rs = stmt.executeQuery(s);

        if (rs.next()) {
          BLOB blob = ((OracleResultSet) rs).getBLOB(1);
          ObjectOutputStream output = new ObjectOutputStream(blob.setBinaryStream(0));
          output.writeObject(individual);
          output.flush();
        }

        rs.close();
        conn.commit();
      }

      stmt.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }

    return ID;
  }

  /** *************************************************************************** */

  public boolean deleteIndividual(int id) throws IOException {
    boolean done = false;
    try {
      Statement stmt = conn.createStatement();
      String s = "DELETE FROM individual WHERE id = " + id;
      stmt.execute(s);
      conn.commit();
      stmt.close();
      done = true;
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return done;
  }

  /** *************************************************************************** */

  public EvIndividualInfo getIndividualInfo(int id,
      boolean with_individual_itself) throws IOException {
    EvIndividualInfo info = null;

    try {

      Statement stmt = conn.createStatement();
      String s = "SELECT * FROM individual WHERE id = " + id;
      ResultSet rs = stmt.executeQuery(s);

      if (rs.next()) {
        info = new EvIndividualInfo();
        info.setID(rs.getInt("id"));
        info.setTaskID(rs.getLong("instance_id"));
        info.setObjectiveValue(rs.getDouble("objective_value"));
        info.setCellID(rs.getLong("creation_cell"));
        info.setNodeID(rs.getLong("creation_node"));
        info.setCreationTime(rs.getTimestamp("creation_time"));

        if (with_individual_itself) {
          BLOB blob = ((OracleResultSet) rs).getBLOB("individual");
          ObjectInputStream input = new ObjectInputStream(blob
              .getBinaryStream());
          info.setIndividual(input.readObject());
        }
      }

      rs.close();
      stmt.close();
    } catch (SQLException e) {
      e.printStackTrace();
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    }

    return info;
  }

  /** *************************************************************************** */

  public EvIndividualInfo[] getBestIndividualInfos(long task_id, int k, int n,
      boolean with_individuals_itselves) throws IOException {
    EvIndividualInfo[] infos = new EvIndividualInfo[n];

    try {
      int i = 0;

      Statement stmt = conn.createStatement();
      String s = "SELECT * FROM (SELECT * FROM individual WHERE instance_id = "
          + task_id + " ORDER BY objective_value DESC) WHERE ROWNUM BETWEEN "
          + k + " AND " + (k + n - 1);
      ResultSet rs = stmt.executeQuery(s);

      while (rs.next()) {
        infos[i] = new EvIndividualInfo();
        infos[i].setID(rs.getInt("id"));
        infos[i].setTaskID(rs.getLong("instance_id"));
        infos[i].setObjectiveValue(rs.getDouble("objective_value"));
        infos[i].setCellID(rs.getLong("creation_cell"));
        infos[i].setNodeID(rs.getLong("creation_node"));
        infos[i].setCreationTime(rs.getTimestamp("creation_time"));

        if (with_individuals_itselves) {
          BLOB blob = ((OracleResultSet) rs).getBLOB("individual");
          ObjectInputStream input = new ObjectInputStream(blob
              .getBinaryStream());
          infos[i].setIndividual(input.readObject());
        }

        i++;
      }

      rs.close();
      stmt.close();
    } catch (SQLException e) {
      e.printStackTrace();
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    }

    return infos;
  }

  public EvIndividualInfo[] getBestIndividualInfosMatchingCell(long task_id,
      long cell_id, int k, int n, boolean with_individuals_itselves)
      throws IOException {
    EvIndividualInfo[] infos = new EvIndividualInfo[n];

    try {
      int i = 0;

      Statement stmt = conn.createStatement();
      String s = "SELECT * FROM (SELECT * FROM individual WHERE instance_id = "
          + task_id + " AND creation_cell = " + cell_id
          + " ORDER BY objective_value DESC) WHERE ROWNUM BETWEEN " + k
          + " AND " + (k + n - 1);
      ResultSet rs = stmt.executeQuery(s);

      while (rs.next()) {
        infos[i] = new EvIndividualInfo();
        infos[i].setID(rs.getInt("id"));
        infos[i].setTaskID(rs.getLong("instance_id"));
        infos[i].setObjectiveValue(rs.getDouble("objective_value"));
        infos[i].setCellID(rs.getLong("creation_cell"));
        infos[i].setNodeID(rs.getLong("creation_node"));
        infos[i].setCreationTime(rs.getTimestamp("creation_time"));

        if (with_individuals_itselves) {
          BLOB blob = ((OracleResultSet) rs).getBLOB("individual");
          ObjectInputStream input = new ObjectInputStream(blob
              .getBinaryStream());
          infos[i].setIndividual(input.readObject());
        }

        i++;
      }

      rs.close();
      stmt.close();
    } catch (SQLException e) {
      e.printStackTrace();
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    }

    return infos;
  }

  public EvIndividualInfo[] getBestIndividualInfosNotMatchingCell(long task_id,
      long cell_id, int k, int n, boolean with_individuals_itselves)
      throws IOException {
    EvIndividualInfo[] infos = new EvIndividualInfo[n];

    try {
      int i = 0;

      Statement stmt = conn.createStatement();
      String s = "SELECT * FROM (SELECT * FROM individual WHERE instance_id = "
          + task_id + " AND creation_cell <> " + cell_id
          + " ORDER BY objective_value DESC) WHERE ROWNUM BETWEEN " + k
          + " AND " + (k + n - 1);
      ResultSet rs = stmt.executeQuery(s);

      while (rs.next()) {
        infos[i] = new EvIndividualInfo();
        infos[i].setID(rs.getInt("id"));
        infos[i].setTaskID(rs.getLong("instance_id"));
        infos[i].setObjectiveValue(rs.getDouble("objective_value"));
        infos[i].setCellID(rs.getLong("creation_cell"));
        infos[i].setNodeID(rs.getLong("creation_node"));
        infos[i].setCreationTime(rs.getTimestamp("creation_time"));

        if (with_individuals_itselves) {
          BLOB blob = ((OracleResultSet) rs).getBLOB("individual");
          ObjectInputStream input = new ObjectInputStream(blob
              .getBinaryStream());
          infos[i].setIndividual(input.readObject());
        }

        i++;
      }

      rs.close();
      stmt.close();
    } catch (SQLException e) {
      e.printStackTrace();
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    }

    return infos;
  }

  public EvIndividualInfo[] getBestIndividualInfosMatchingNode(long task_id,
      long cell_id, int k, int n, boolean with_individuals_itselves)
      throws IOException {
    EvIndividualInfo[] infos = new EvIndividualInfo[n];

    try {
      int i = 0;

      Statement stmt = conn.createStatement();
      String s = "SELECT * FROM (SELECT * FROM individual WHERE instance_id = "
          + task_id + " AND creation_node = " + cell_id
          + " ORDER BY objective_value DESC) WHERE ROWNUM BETWEEN " + k
          + " AND " + (k + n - 1);
      ResultSet rs = stmt.executeQuery(s);

      while (rs.next()) {
        infos[i] = new EvIndividualInfo();
        infos[i].setID(rs.getInt("id"));
        infos[i].setTaskID(rs.getLong("instance_id"));
        infos[i].setObjectiveValue(rs.getDouble("objective_value"));
        infos[i].setCellID(rs.getLong("creation_cell"));
        infos[i].setNodeID(rs.getLong("creation_node"));
        infos[i].setCreationTime(rs.getTimestamp("creation_time"));

        if (with_individuals_itselves) {
          BLOB blob = ((OracleResultSet) rs).getBLOB("individual");
          ObjectInputStream input = new ObjectInputStream(blob
              .getBinaryStream());
          infos[i].setIndividual(input.readObject());
        }

        i++;
      }

      rs.close();
      stmt.close();
    } catch (SQLException e) {
      e.printStackTrace();
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    }

    return infos;
  }

  /** *************************************************************************** */

  public int getIndividualCount(long task_id) throws IOException {
    int count = -1;

    try {
      Statement stmt = conn.createStatement();
      String s = "SELECT COUNT(*) FROM individual WHERE instance_id = "
          + task_id;
      ResultSet rs = stmt.executeQuery(s);

      if (rs.next())
        count = rs.getInt(1);

      rs.close();
      stmt.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }

    return count;
  }

  public int getIndividualCountMatchingCell(long task_id, long cell_id)
      throws IOException {
    int count = -1;

    try {
    

      Statement stmt = conn.createStatement();
      String s = "SELECT COUNT(*) FROM individual WHERE instance_id = "
          + task_id + "AND creation_cell = " + cell_id;
      ResultSet rs = stmt.executeQuery(s);

      if (rs.next())
        count = rs.getInt(1);

      rs.close();
      stmt.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }

    return count;
  }

  public int getIndividualCountNotMatchingCell(long task_id, long cell_id)
      throws IOException {
    int count = -1;

    try {
    

      Statement stmt = conn.createStatement();
      String s = "SELECT COUNT(*) FROM individual WHERE instance_id = "
          + task_id + "AND creation_cell <> " + cell_id;
      ResultSet rs = stmt.executeQuery(s);

      if (rs.next())
        count = rs.getInt(1);

      rs.close();
      stmt.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }

    return count;
  }

  public int getIndividualCountMatchingNode(long task_id, long cell_id)
      throws IOException {
    int count = -1;

    try {
     

      Statement stmt = conn.createStatement();
      String s = "SELECT COUNT(*) FROM individual WHERE instance_id = "
          + task_id + "AND creation_node = " + cell_id;
      ResultSet rs = stmt.executeQuery(s);

      if (rs.next())
        count = rs.getInt(1);

      rs.close();
      stmt.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }

    return count;
  }

  public void deleteIndividualsFromTask(long task_id) throws IOException {
    try {
      Statement stmt = conn.createStatement();
      String s = "DELETE FROM individual WHERE instance_id=" + task_id;
      stmt.execute(s);
      conn.commit();
      stmt.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public int addSolutionSpace(long task_id, long cell_id, Object space)
      throws IOException {
    try {
      Statement stmt = conn.createStatement();
      int iteration = -1;

      String s = "SELECT iteration_seq.NEXTVAL FROM DUAL";
      ResultSet rs = stmt.executeQuery(s);

      if (rs.next())
        iteration = rs.getInt(1);
      rs.close();

      if (iteration != -1) {
        s = "INSERT INTO solutionspaces (instance_id, cell_id, iteration, solution_space) VALUES("
            + task_id + ", " + cell_id + "," + iteration + ", EMPTY_BLOB())";
        stmt.executeUpdate(s);

        s = "SELECT solution_space FROM solutionspaces WHERE iteration = "
            + iteration + " FOR UPDATE";
        rs = stmt.executeQuery(s);

        if (rs.next()) {
          BLOB blob = ((OracleResultSet) rs).getBLOB(1);
          ObjectOutputStream output = new ObjectOutputStream(blob.setBinaryStream(0));
          output.writeObject(space);
          output.flush();
        }

        rs.close();
        conn.commit();
      }

      stmt.close();
      return iteration;
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return -1;
  }

  public Object getSolutionSpace(long task_id, long cell_id) throws IOException {

    Object space = null;

    try {

      Statement stmt = conn.createStatement();
      String s = "SELECT solution_space FROM solutionspaces WHERE instance_id="
          + task_id
          + "AND cell_id="
          + cell_id
          + " AND iteration = (SELECT MAX(iteration) FROM solutionspaces WHERE instance_id="
          + task_id + " AND cell_id=" + cell_id + ")";
      ResultSet rs = stmt.executeQuery(s);

      if (rs.next()) {
        BLOB blob = ((OracleResultSet) rs).getBLOB("solution_space");
        ObjectInputStream input = new ObjectInputStream(blob.getBinaryStream());
        space = input.readObject();
      }

      rs.close();
      stmt.close();
    } catch (SQLException e) {
      e.printStackTrace();
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    }

    return space;
  }

  public boolean deleteSolutionSpaces(long task_id) throws IOException {
    try {
      Statement stmt = conn.createStatement();
      String s = "DELETE FROM solutionspaces WHERE instance_id=" + task_id;
      stmt.execute(s);
      conn.commit();
      stmt.close();
      return true;
    } catch (SQLException e) {
      e.printStackTrace();
    }

    return false;
  }

  public Long[] getTaskIDs() {
    ArrayList<Long> list = new ArrayList<Long>();

    try {
      Statement stmt = conn.createStatement();
      String s = "SELECT DISTINCT instance_id FROM individual";
      ResultSet rs = stmt.executeQuery(s);

      while (rs.next()) {
        list.add(rs.getLong(1));
      }

      rs.close();
      stmt.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }

    if (list.size() != 0) {
      Long[] res = new Long[list.size()];

      for (int i = 0; i < list.size(); i++) {
        res[i] = list.get(i);
      }

      return res;
    }

    return null;
  }

  public int getVersionOfNewSolutonSpace(long task_id, long cell_id) {
    int count = 0;

    try {

      Statement stmt = conn.createStatement();
      String s = "SELECT MAX(iteration) FROM solutionspaces WHERE instance_id="
          + task_id + " AND cell_id=" + cell_id;
      ResultSet rs = stmt.executeQuery(s);

      if (rs.next())
        count = rs.getInt(1);

      rs.close();
      stmt.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }

    return count;
  }

  public EvTaskInfo getTaskForSystem(int id) throws IOException {
    EvTaskInfo info = null;

    try {
      Statement stmt = conn.createStatement();
      String s = "SELECT * FROM tasks WHERE id=" + id;
      ResultSet rs = stmt.executeQuery(s);

      if (rs.next()) {
        BLOB blob = ((OracleResultSet) rs).getBLOB("jar");
        ObjectInputStream input = new ObjectInputStream(blob.getBinaryStream());
        Object o = input.readObject();
        ;

        info = new EvTaskInfo();
        info.setId(rs.getInt("id"));
        info.setJar(o);
        info.setStatus(rs.getInt("status"));
        info.setDescription(rs.getString("description"));
        info.setSubmissionTime(rs.getTimestamp("submission_time"));

      }
      rs.close();
      stmt.close();
    } catch (SQLException e) {
      e.printStackTrace();
    } catch (Exception e) {
    }
    return info;
  }

  public int addTaskForSystem(byte[] file, String desc) throws IOException {
    int id = -1;
    try {
      Statement stmt = conn.createStatement();
      String s = "SELECT tasks_seq.NEXTVAL FROM DUAL";
      ResultSet rs = stmt.executeQuery(s);

      if (rs.next())
        id = rs.getInt(1);
      rs.close();

      if (id != -1) {
        s = "INSERT INTO tasks(id, jar, submission_time, status, description) VALUES("
            + id + ", EMPTY_BLOB(), CURRENT_TIMESTAMP, 1, '" + desc + "')";
        stmt.executeUpdate(s);

        s = "SELECT jar FROM tasks WHERE id = " + id + " FOR UPDATE";
        rs = stmt.executeQuery(s);

        if (rs.next()) {
          BLOB blob = ((OracleResultSet) rs).getBLOB(1);
          ObjectOutputStream output = new ObjectOutputStream(blob.setBinaryStream(0));
          output.writeObject(file);
          output.flush();
        }

        rs.close();
        conn.commit();
      }

      stmt.close();
    } catch (SQLException e) {
      e.printStackTrace();
      id = -2;
    } catch (Exception e) {
      id = -3;
    }

    return id;
  }

  public void deleteTaskFromSystem(int id) throws IOException {
    try {
      Statement stmt = conn.createStatement();
      String s = "DELETE FROM tasks WHERE id = " + id;
      stmt.execute(s);
      conn.commit();
      stmt.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public Integer[] getTaskIDsForSystem() throws IOException {
    ArrayList<Integer> list = new ArrayList<Integer>();

    try {
      Statement stmt = conn.createStatement();
      String s = "SELECT id FROM tasks";
      ResultSet rs = stmt.executeQuery(s);

      while (rs.next()) {
        list.add(rs.getInt(1));
      }

      rs.close();
      stmt.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }

    if (list.size() != 0) {
      Integer[] res = new Integer[list.size()];

      for (int i = 0; i < list.size(); i++) {
        res[i] = list.get(i);
      }

      return res;
    }

    return null;
  }

  public void changeTaskState(int id, int state) throws IOException {
    try {
      Statement stmt = conn.createStatement();
      String s = "UPDATE tasks SET status=" + state + " WHERE id = " + id;
      stmt.execute(s);
      conn.commit();
      stmt.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }

  }

  public boolean deleteResource(String name) throws IOException {
    try {
      Statement stmt = conn.createStatement();
      String s = "DELETE FROM resources WHERE name='" + name + "'";
      stmt.execute(s);
      conn.commit();
      stmt.close();
    } catch (SQLException e) {
      e.printStackTrace();
      return false;
    }
    return true;
  }

  public Object getResource(String name) throws IOException {
    Object res = null;
    try {
      Statement stmt = conn.createStatement();
      String s = "SELECT * FROM resources WHERE name='" + name + "'"; 
      ResultSet rs = stmt.executeQuery(s);

      if (rs.next()) {
        BLOB blob = ((OracleResultSet) rs).getBLOB("res");
        ObjectInputStream input = new ObjectInputStream(blob.getBinaryStream());
        res = input.readObject();
        

      }
      rs.close();
      stmt.close();
      return res;
      
    } catch (SQLException e) {
      e.printStackTrace();
    } catch (Exception e) {
    }
    return null;
  }

  public String[] getResourceNames() throws IOException {
    ArrayList<String> list = new ArrayList<String>();

    try {
      Statement stmt = conn.createStatement();
      String s = "SELECT name FROM resources";
      ResultSet rs = stmt.executeQuery(s);

      while (rs.next()) {
        list.add((String) rs.getObject(1));
      }

      rs.close();
      stmt.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }

    if (list.size() != 0) {
      String[] res = new String[list.size()];

      for (int i = 0; i < list.size(); i++) {
        res[i] = list.get(i);
      }

      return res;
    }

    return null;
  }


  public boolean setResource(Object res, String name) throws IOException {

    try {
      deleteResource(name);

      Statement stmt = conn.createStatement();

      String s = "INSERT INTO resources(name, res) VALUES('" + name + "',EMPTY_BLOB())";
      stmt.executeUpdate(s);

      s = "SELECT res FROM resources WHERE name='" + name + "' FOR UPDATE";
      ResultSet rs = stmt.executeQuery(s);

      if (rs.next()) {
        BLOB blob = ((OracleResultSet) rs).getBLOB(1);
        ObjectOutputStream output = new ObjectOutputStream(blob.setBinaryStream(0));
        output.writeObject(res);
        output.flush();
      }

      rs.close();
      conn.commit();
      stmt.close();
      return true;
      
    } catch( Exception ex) {
      ex.printStackTrace();

    }
    return false;
      
  }

  public Long[] getCellIdsWithStatistics(long task_id) {
    // TODO Auto-generated method stub
    return null;
  }

  public Long[] getNodesIdsWithStatistics(long task_id, long node_id) {
    // TODO Auto-generated method stub
    return null;
  }

  public Object[] getStatistics(long task_id, long cell_id, long node_id) {
    // TODO Auto-generated method stub
    return null;
  }

  public Long[] getTaskIdsWithStatistics() {
    // TODO Auto-generated method stub
    return null;
  }

  public void saveStatistic(long task_id, long cell_id, long node_id, Object stat, long iteration, long memory) {
    // TODO Auto-generated method stub
    
  }

  public void deleteStatisticForTask(long task_id) {
    // TODO Auto-generated method stub
  }

  public Object[] getStatisticsByCells(long taks_id, int[] cells) {
    // TODO Auto-generated method stub
    return null;
  }

  public Object[] getStatisticsByID(long task_id) {
    // TODO Auto-generated method stub
    return null;
  }

  public Object[] getStatisticsByNodes(long taks_id, int[] nodes) {
    // TODO Auto-generated method stub
    return null;
  }

}
